package abmb.utils;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
/***
 * This class contains methods concerning the business logic of the Web application.
 * @author Andrea Balboni
 * @version 1.0  
 */
public class Utilities {
	
	/**
	 * connection object
	 */
	public static Connection conn=null;

	/**
	 * Establish the connection to the datasource using the context's resource.
	 * 
	 * @return Object with connection to the datasource
	 */
	public static Connection getConnection(){
		Connection connection = null;
		try {
			InitialContext context = new InitialContext();
			DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/andrea");
			connection = dataSource.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * Class constructor, used to associate the connection object with the DB connection
	 */
	public Utilities(){
		conn=getConnection();
	}

	/**
	 * This method adds records to the database for the association of users with followed users.
	 * 
	 * @param userID Identifier of the "follower" user.
	 * @param userSelectionID List of identifiers of the users that are to be "followed".
	 * @return Boolean value representing the success of the transaction.
	 */
	public boolean addFollowed(Integer userID, String[] userSelectionID) {
		String iQuery="INSERT INTO followers(Follower,Followed) VALUES(?,?)";
		try {
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(iQuery);
			ps.setInt(1,userID.intValue());
			for(int i=0;i<userSelectionID.length;++i){
				ps.setString(2, userSelectionID[i]);
				int rs=ps.executeUpdate();
				if(rs==0){
					return false;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	
	/**
	 * This method adds records to the database associating tags with a particular post.
	 * @param postID Identifier of the post in the microblog for which the tags are intended.
	 * @param tags ArrayList of string representing tags for the specified post.
	 */
	private void associateTags(int postID, ArrayList<String> tags) {
		ArrayList<Integer> tagIDs=new ArrayList<Integer>();
		for(String t:tags){
			if(checkTag(t)){
				tagIDs.add(getTagID(t));
			}else{
				tagIDs.add(insertTagAndGetID(t));
			}
		}
		String assTags="INSERT INTO thread_tag(IDThread,IDTag) VALUES(?,?)";
		try {
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps3=conn.prepareStatement(assTags);

			for(Integer tID:tagIDs){
				ps3.setInt(1, postID);
				ps3.setInt(2, tID);
				ps3.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	/**
	 * This method queries the database to check if the specified string is an existing tag.
	 * @param t String representing the tag
	 * @return Boolean value true if the specified tag exists.
	 */
	private boolean checkTag(String t) {
		//Connection conn=getConnection();
		boolean found=true;
		String qCheckTag="SELECT count(*) FROM tags WHERE TagName=?";
		PreparedStatement ps1;
		try {
			if(conn.isClosed())
				conn=getConnection();
			ps1 = conn.prepareStatement(qCheckTag);
			ps1.setString(1, t);
			ResultSet rs=ps1.executeQuery();

			int tagCount = 0;
			while(rs.next()){
				tagCount=rs.getInt(1);
			}
			if(tagCount==0) //user not registered
				found=false;

			ps1.close();
			rs.close();
			//conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return found;

	}

	/**
	 * This method queries the database to check if a username is already used by some user.
	 * @param username String representing the desired username for registration to the microblog.
	 * @return Boolean value true if the username exists in the database.
	 */
	public final boolean checkUserRegistration(String username){
		//Connection conn=getConnection();
		boolean found=true;
		String qCheckNick="SELECT count(*) FROM users WHERE Nickname=?";
		PreparedStatement ps1;
		try {
			if(conn.isClosed())
				conn=getConnection();
			ps1 = conn.prepareStatement(qCheckNick);
			ps1.setString(1, username);
			ResultSet rs=ps1.executeQuery();

			int nickCount = 0;
			while(rs.next()){
				nickCount=rs.getInt(1);
			}
			if(nickCount==0) //user not registered
				found=false;

			ps1.close();
			rs.close();
			//conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return found;
	}

	/**
	 * Deletes user's association to specified microblogs from the database.
	 * @param userID Identifier of the "follower" user.
	 * @param userSelectionID List of identifiers of users which microblogs are to be deleted from associations.
	 * @return Boolean value representing the success of the transaction.
	 */
	public boolean deleteFollowed(Integer userID, String[] userSelectionID) {
		String dQuery="DELETE FROM followers WHERE follower=? AND followed=?";
		try {
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(dQuery);
			ps.setInt(1,userID.intValue());
			for(int i=0;i<userSelectionID.length;++i){
				ps.setString(2, userSelectionID[i]);
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	/**
	 * Deletes from the database the tags associated to a specified post.
	 * @param postID Identifier of the post for wich tags are being deleted.
	 */
	private void deleteTags(int postID) {
		String dQuery="DELETE FROM thread_tag WHERE IDThread=?";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(dQuery);
			ps.setInt(1, postID);
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();	
		}
	}
	
	/**
	 * Deletes a post from the database.
	 * @param pID Identifier of the post that is to be deleted.
	 * @return Boolean value representing the success of the operation.
	 */
	public boolean doDeleteMessage(int pID) {
		String dQuery=null;

		dQuery="DELETE FROM threads where IDThread=?";
		deleteTags(pID);
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(dQuery);
			ps.setInt(1, pID);

			ps.executeUpdate();

			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	/**
	 * This method is used to edit a post of a microblog updating values in the database.
	 * @param post Object containing post informations.
	 * @return Boolean value representing the success of the transaction.
	 */
	public boolean doEditMessage(MBPost post) {
		String iQuery=null;

		iQuery="UPDATE threads SET IDUser=?, ThreadTitle=?, ThreadMessage=?, Date=? WHERE IDThread="+post.postID();

		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(iQuery);
			if(post.userID()>=0)
				ps.setInt(1, post.userID());
			ps.setString(2, post.title());
			ps.setString(3, post.body());
			ps.setTimestamp(4,new java.sql.Timestamp(post.postDate().getTime()));
			int rs=ps.executeUpdate();
			if(rs==0){
				return false;
			}
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		String getPid="SELECT IDThread FROM threads WHERE IDUser=? AND ThreadTitle LIKE '"+post.title()+"' AND ThreadMessage LIKE '"+post.body()+"' AND Date=?";
		try {
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps2=conn.prepareStatement(getPid);
			ps2.setInt(1, post.userID());
			ps2.setTimestamp(2,new java.sql.Timestamp(post.postDate().getTime()));
			ResultSet rs2=ps2.executeQuery();
			while(rs2.next()){
				post.postID(rs2.getInt("IDThread"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		deleteTags(post.postID());
		associateTags(post.postID(),post.tags());
		return true;
	}
	
	/**
	 * This method check's user credentials and sets the HTTP session attributes for logging in.
	 * @param username String representing the username of the user that is logging in.
	 * @param password String representing the password of the user that is logging in.
	 * @param session HTTPSession of the serlvet.
	 * @param p PrintWriter used to print messages in the page generated for the user.
	 * @return Boolean value representing the success of the operation.
	 */
	public boolean doLogin(String username, String password, HttpSession session, PrintWriter p){
		//Connection conn=getConnection();
		int userID=-1;
		String message="",name="",lastname="",gUserName="";
		boolean userfound=false, success=false;
		String qLogin="SELECT ID, Name, LastName, Nickname FROM users WHERE Nickname like ? AND Password like SHA1('"+password+"')";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement pst=conn.prepareStatement(qLogin);
			pst.setString(1, username);
			ResultSet res=pst.executeQuery();
			while(res.next()){
				gUserName=res.getString("Nickname");
				if(gUserName.equals(username)){
					userfound=true;
				}
				name=res.getString("Name");
				lastname=res.getString("LastName");
				userID=res.getInt("ID");
			}
			if(gUserName.equals(username) && userID!=-1){
				session.setAttribute("loggedin","true");
				session.setAttribute("name", name);
				session.setAttribute("lastname",lastname);
				session.setAttribute("nickname",gUserName);
				session.setAttribute("userID",userID);
				success=true;
				//response.sendRedirect(response.encodeRedirectURL("index"));
			}else if(userfound){
				message="Wrong password for user "+gUserName+" please try again";
			}
			p.println("<p>"+message+"</p>");
			pst.close();
			res.close();
			//conn.close();
		} catch (SQLException e) {
			p.println("Exception: "+e.getLocalizedMessage().toString());
			e.printStackTrace();
		}

		return success;
	}
	
	/**
	 * This method updates the database inserting a new post or modifying an existing one.
	 * @param post Object of type MBPost containing a post.
	 * @return Boolean value representing the success of the transaction.
	 */
	public boolean doPostMessage(MBPost post) {
		String iQuery=null;
		if(post.postID()<=0)
			iQuery="INSERT INTO threads(IDUser,ThreadTitle,ThreadMessage,Date) VALUES(?,?,?,?)";
		else{
			iQuery="UPDATE threads SET IDUser=?, ThreadTitle=?, ThreadMessage=?, Date=? WHERE IDThread="+post.postID();
		}
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(iQuery);
			if(post.userID()>=0)
				ps.setInt(1, post.userID());
			ps.setString(2, post.title());
			ps.setString(3, post.body());
			ps.setTimestamp(4,new java.sql.Timestamp(post.postDate().getTime()));
			int rs=ps.executeUpdate();
			if(rs==0){
				return false;
			}
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		String getPid="SELECT IDThread FROM threads WHERE IDUser=? AND ThreadTitle LIKE '"+post.title()+"' AND ThreadMessage LIKE '"+post.body()+"' AND Date=?";
		try {
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps2=conn.prepareStatement(getPid);
			ps2.setInt(1, post.userID());
			ps2.setTimestamp(2,new java.sql.Timestamp(post.postDate().getTime()));
			ResultSet rs2=ps2.executeQuery();
			while(rs2.next()){
				post.postID(rs2.getInt("IDThread"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		associateTags(post.postID(),post.tags());
		return true;
	}

	/**
	 * This method is used to search the database for a given keyword.
	 * @param keyword
	 * @return ArrayList of objects of type MBPost containing the posts in the system satisfying the search criteria.
	 */
	public ArrayList<MBPost> doSearch(String keyword){
		ArrayList<MBPost> postList=new ArrayList<MBPost>();
		postList.clear();
		postList.addAll(this.getPostsByIDs(this.findPostsByGenericString(keyword)));
		if(!postList.isEmpty())
			Collections.sort(postList, new DateComparator());
		return postList;
	}
	
	/**
	 * This method is used to search the database for a given keyword representing a tag.
	 * @param tagsearchkey Keyword representing a tag.
	 * @return ArrayList of objects of type MBPost containing the posts in the system associated to the given tag.
	*/
	public ArrayList<MBPost> doSearchTag(String tagsearchkey) {
		ArrayList<MBPost> postList=new ArrayList<MBPost>();
		postList.clear();
		postList.addAll(this.getPostsByIDs(this.findPostsByTagName(tagsearchkey)));
		if(!postList.isEmpty())
			Collections.sort(postList, new DateComparator());
		return postList;
	}
	
	/**
	 * This method updates the database adding a new user registration.
	 * @param FName First name of the user that is signing up.
	 * @param LName Last name of the user that is signing up.
	 * @param NName Username of the user that is signing up.
	 * @param Pass Password choosen by the user.
	 * @param p PrintWriter to add messages to the generated page.
	 * @return Boolean value representing the success of the operation.
	 */
	public boolean doSubscribe(String FName, String LName, String NName, String Pass,PrintWriter p){
		String message="";
		boolean success=false;
		int qRes=0;
		String query="INSERT INTO users(Name,LastName,Nickname,Password) VALUES (?,?,?,SHA1('"+Pass+"')) ";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement ps=conn.prepareStatement(query);
			ps.setString(1, FName);
			ps.setString(2, LName);
			ps.setString(3, NName);
			qRes=ps.executeUpdate();

			if(qRes==0){
				success=false;
				message="Warning: your subscription failed, please check your submission data";
			}else{
				success=true;
				message="Subscription Accepted, click <a href="+PageLinks.getIndexLink()+">here</a> to start";
			}
			ps.close();
			p.println("<p>"+message+"</p>");

		} catch (SQLException e) {
			e.printStackTrace();
			p.println("<p>Exception: "+e.getLocalizedMessage()+"</p>");

		}
		return success;
	}
	
	/**
	 * Destructor of the class that closes the connection to the database.
	 */
	protected void finalize() throws Throwable
	{
		conn.close();
		super.finalize();
	}
	
	/**
	 * This methods retrieves from the database all the users that match the given key whose microblog can be followed by the specified user.
	 * @param userKey String to identify a user, it can represent the username, the first name or the last name.
	 * @param uID Identifier of the logged-in user.
	 * @return ArrayList of objects of type MBUser containing informations on the searched users.
	 */
	public ArrayList<MBUser> findFollowableUserListByKey(String userKey,Integer uID) {
		ArrayList<MBUser> uList=new ArrayList<MBUser>();
		uList.clear();
		String usersQueryS="Select ID, Name, LastName, Nickname FROM users WHERE (Nickname like '"+userKey+"%' OR Name like '"+userKey+"%' OR LastName like '"+userKey+"%') AND ID<>? AND ID NOT IN(SELECT DISTINCT Followed FROM followers WHERE Follower=?)";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement pst=conn.prepareStatement(usersQueryS);
			pst.setInt(1,uID.intValue());
			pst.setInt(2,uID.intValue());
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				MBUser user=new MBUser();
				user.clear();
				user.uID(rs.getInt("ID"));
				user.uName(rs.getString("Name"));
				user.uLastName(rs.getString("LastName"));
				user.uNickName(rs.getString("Nickname"));
				uList.add(user);
			}
		}catch (SQLException e){
			System.out.println("getUserList: "+e.getLocalizedMessage());
			e.printStackTrace();
		}
		return uList;
	}
	
	/**
	 * This methods retrieves from the database all the users whose microblog can be followed by the specified user.
	 * @param uID Identifier of the logged-in user.
	 * @return ArrayList of objects of type MBUser containing informations on the searched users.
	 */
	public ArrayList<MBUser> findFollowableUsers(Integer uID) {
		ArrayList<MBUser> uList=new ArrayList<MBUser>();
		uList.clear();
		String usersQuery="SELECT ID, Name, LastName, Nickname FROM users where ID<>"+uID+" AND ID NOT IN (SELECT DISTINCT Followed FROM followers WHERE Follower="+uID+")";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement pst=conn.prepareStatement(usersQuery);

			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				MBUser user=new MBUser();
				user.clear();
				user.uID(rs.getInt("ID"));
				user.uName(rs.getString("Name"));
				user.uLastName(rs.getString("LastName"));
				user.uNickName(rs.getString("Nickname"));
				uList.add(user);
			}
		}catch (SQLException e){
			System.out.println("getUserList: "+e.getLocalizedMessage());
			e.printStackTrace();
		}
		return uList;
	}
	
	/**
	 * This method retrieves from the database all the users that are currently followed by the logged-in user.
	 * @param uID Identifier of the logged-in user.
	 * @return ArrayList of objects of type MBUser containing informations on the followed users.
	 */
	public ArrayList<MBUser> findFollowedUsers(Integer uID) {
		ArrayList<MBUser> uList=new ArrayList<MBUser>();
		uList.clear();
		String usersQuery="Select ID, Name, LastName, Nickname FROM users where ID in (SELECT DISTINCT Followed FROM followers WHERE Follower="+uID+")";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement pst=conn.prepareStatement(usersQuery);

			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				MBUser user=new MBUser();
				user.clear();
				user.uID(rs.getInt("ID"));
				user.uName(rs.getString("Name"));
				user.uLastName(rs.getString("LastName"));
				user.uNickName(rs.getString("Nickname"));
				uList.add(user);
			}
		}catch (SQLException e){
			System.out.println("getUserList: "+e.getLocalizedMessage());
			e.printStackTrace();
		}
		return uList;
	}

	public ArrayList<Integer> findPostsByGenericString(String sString){
		ArrayList<Integer> pospIDs=new ArrayList<Integer>();
		String query="SELECT DISTINCT IDThread FROM vallthreads WHERE Nickname like '"+sString+"' OR ThreadTitle like '%"+sString+"%' OR ThreadMessage like '%"+sString+"%' OR IDThread IN (SELECT IDThread from thread_tag join tags on thread_tag.IDTag=tags.IDTAG WHERE tags.TagName like '"+sString+"') ORDER BY Date DESC";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement pst=conn.prepareStatement(query);
			pospIDs.clear();
			ResultSet res=pst.executeQuery();
			while(res.next()){
				pospIDs.add(res.getInt(1));
			}
			pst.close();
			res.close();
		} catch (SQLException e) {
			System.out.println("findPostsByGenericString: "+e.toString());
			e.printStackTrace();
		}

		return pospIDs;
	}

	public ArrayList<Integer> findPostsByMessageString(String searchString){
		ArrayList<Integer> pospIDs=new ArrayList<Integer>();
		String query="SELECT DISTINCT THR.IDThread FROM threads THR WHERE THR.ThreadMessage like '_%"+searchString+"%' ORDER BY THR.Date DESC";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement pst=conn.prepareStatement(query);
			ResultSet res=pst.executeQuery();
			while(res.next()){
				pospIDs.add(res.getInt(1));
			}
			pst.close();
			res.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println("findPostsByMessageString: "+e.toString());
			e.printStackTrace();
		}
		return pospIDs;
	}


	public ArrayList<Integer> findPostsByTagName(String tagName){
		ArrayList<Integer> pospIDs=new ArrayList<Integer>();
		String query="SELECT DISTINCT THR.IDThread FROM threads THR JOIN thread_tag TT ON THR.IDThread=TT.IDThread JOIN tags TA on TA.IDTAG=TT.IDTag WHERE TA.TagName like '"+tagName+"' ORDER BY THR.Date DESC";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement pst=conn.prepareStatement(query);
			ResultSet res=pst.executeQuery();
			pospIDs.clear();
			while(res.next()){
				pospIDs.add(res.getInt(1));
			}
		} catch (SQLException e) {
			System.out.println("findPostsByTagName: "+e.toString());
			e.printStackTrace();
		}
		return pospIDs;
	}

	public ArrayList<Integer> findPostsByUsername(String username){
		ArrayList<Integer> postsID=new ArrayList<Integer>();
		String query="SELECT DISTINCT THR.IDThread FROM threads THR JOIN users U on U.ID=THR.IDUser WHERE U.Nickname like ? ORDER BY THR.Date DESC";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement pst=conn.prepareStatement(query);
			pst.setString(1, username);
			ResultSet res=pst.executeQuery();
			while(res.next()){
				postsID.add(res.getInt(1));
			}
			pst.close();
			res.close();
		} catch (SQLException e) {
			System.out.println("findPostssByUsername: "+e.toString());
			e.printStackTrace();
		}

		return postsID;
	}

	public ArrayList<Integer> findPostsIFollow(int userID){
		ArrayList<Integer> postsIDs=new ArrayList<Integer>();
		String query="SELECT Distinct IDThread FROM vallthreads WHERE IDUser IN (SELECT Followed FROM followers WHERE Follower="+userID+") AND IDUser <> "+userID+" ORDER BY Date DESC";
		try {
			if(conn.isClosed())
				conn=getConnection();
			PreparedStatement pst=conn.prepareStatement(query);
			ResultSet res=pst.executeQuery();
			while(res.next()){
				postsIDs.add(res.getInt(1));
			}
			pst.close();
			res.close();
		} catch (SQLException e) {
			System.out.println("findPostsIFollow: "+e.toString());
			e.printStackTrace();
		}
		return postsIDs;
	}
	public ArrayList<MBUser> findUserList(String userKey) {
		ArrayList<MBUser> uList=new ArrayList<MBUser>();
		uList.clear();
		String usersQueryS="Select ID, Name, LastName, Nickname FROM users WHERE Nickname like '"+userKey+"%' OR Name like '"+userKey+"%' OR LastName like '"+userKey+"%'";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement pst=conn.prepareStatement(usersQueryS);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				MBUser user=new MBUser();
				user.clear();
				user.uID(rs.getInt("ID"));
				user.uName(rs.getString("Name"));
				user.uLastName(rs.getString("LastName"));
				user.uNickName(rs.getString("Nickname"));
				uList.add(user);
			}

		}catch (SQLException e){
			System.out.println("getUserList: "+e.getLocalizedMessage());
			e.printStackTrace();
		}
		return uList;
	}
	
	public MBPost getPostsByID(int pID) {
		String query="SELECT Name, LastName, Nickname, IDUser, ThreadTitle, ThreadMessage, Date FROM VAllThreads WHERE IDThread=? ORDER BY Date DESC";
		String qTags="Select TA.TagName FROM tags TA JOIN thread_tag TT on TA.IDTAG=TT.IDTag WHERE IDThread=?";
		MBPost post=new MBPost();
		try{
			if(conn.isClosed())
				conn=getConnection();

			PreparedStatement pst=conn.prepareStatement(query);
			pst.setInt(1, pID);
			ResultSet res=pst.executeQuery();
			while(res.next()){
				post.postID(pID);
				post.title(res.getString("ThreadTitle"));
				post.author(res.getString("Nickname")+" ("+res.getString("Name")+" "+res.getString("LastName")+")");
				post.username(res.getString("Nickname"));
				post.userID(res.getInt("IDUser"));
				try {
					post.body(res.getString("ThreadMessage"));
				} catch (Exception e) {
					System.out.println("getPostsByIDs: "+e.toString());
					e.printStackTrace();
				}
				post.setPostDate(res.getTimestamp("Date"));
			}
			pst.close();
			res.close();
			PreparedStatement ps1=conn.prepareStatement(qTags);
			ps1.setInt(1, pID);
			ResultSet rs1=ps1.executeQuery();
			post.tags().clear();
			while(rs1.next()){
				post.tags().add(rs1.getString(1));
			}
			rs1.close();
			ps1.close();
		}catch (SQLException e){
			System.out.println("getThreadsByIDs: "+e.getLocalizedMessage());
			e.printStackTrace();
		}
		return post;
	}

	public ArrayList<MBPost> getPostsByIDs(ArrayList<Integer> postIDs){

		ArrayList<MBPost> postList=new ArrayList<MBPost>();
		postList.clear();

		String query="SELECT Name, LastName, Nickname, IDUser, ThreadTitle, ThreadMessage, Date FROM VAllThreads WHERE IDThread=? ORDER BY Date DESC";
		String qTags="Select TA.TagName FROM tags TA JOIN thread_tag TT on TA.IDTAG=TT.IDTag WHERE IDThread=?";
		for(Integer pID: postIDs){
			MBPost post=new MBPost();
			try{
				if(conn.isClosed())
					conn=getConnection();

				PreparedStatement pst=conn.prepareStatement(query);
				pst.setInt(1, pID);
				ResultSet res=pst.executeQuery();
				while(res.next()){
					post.postID(pID);
					post.userID(res.getInt("IDUser"));
					post.title(res.getString("ThreadTitle"));
					post.author(res.getString("Nickname")+" ("+res.getString("Name")+" "+res.getString("LastName")+")");
					post.username(res.getString("Nickname"));
					try {
						post.body(res.getString("ThreadMessage"));
					} catch (Exception e) {
						System.out.println("getThreadsByIDs: "+e.toString());
						e.printStackTrace();
					}
					post.setPostDate(res.getTimestamp("Date"));
				}
				pst.close();
				res.close();
				PreparedStatement ps1=conn.prepareStatement(qTags);
				ps1.setInt(1, pID);
				ResultSet rs1=ps1.executeQuery();
				post.tags().clear();
				while(rs1.next()){
					post.tags().add(rs1.getString(1));
				}
				rs1.close();
				ps1.close();
				postList.add(post);

			}catch (SQLException e){
				System.out.println("getPostsByIDs: "+e.getLocalizedMessage());
				e.printStackTrace();
			}
		}
		return postList;

	}
	private Integer getTagID(String t) {
		String getTid="SELECT IDTAG FROM tags WHERE TagName=?";
		int tagID=-1;
		try {
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(getTid);
			ps.setString(1,t);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				tagID=rs.getInt("IDTAG");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tagID;
	}
	public ArrayList<MBUser> getUserList() {
		ArrayList<MBUser> uList=new ArrayList<MBUser>();
		uList.clear();
		String usersQuery="Select ID, Name, LastName, Nickname FROM users";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement pst=conn.prepareStatement(usersQuery);

			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				MBUser user=new MBUser();
				user.clear();
				user.uID(rs.getInt("ID"));
				user.uName(rs.getString("Name"));
				user.uLastName(rs.getString("LastName"));
				user.uNickName(rs.getString("Nickname"));
				uList.add(user);
			}

		}catch (SQLException e){
			System.out.println("getUserList: "+e.getLocalizedMessage());
			e.printStackTrace();
		}
		return uList;
	}

	private Integer insertTagAndGetID(String t) {
		String insTag="INSERT INTO tags(TAGNAME) VALUES(?)";
		try{
			if(conn.isClosed()){
				conn=getConnection();
			}
			PreparedStatement ps=conn.prepareStatement(insTag);
			ps.setString(1, t);
			int rs=ps.executeUpdate();
			if(rs==0){
				return -1;
			}
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return getTagID(t);
	}
	
	public boolean isLoggedIn(HttpSession session){
		String lin=(String)session.getAttribute("loggedin");
		if(lin!=null && lin.equals("true"))
			return true;
		else
			return false;
	}

}
